package blackhouse_server;

import java.sql.SQLException;
import java.util.List;

import blackhouse_dao.subject_dao;
import blackhouse_entity.subject;

public class subject_server
{
	subject_dao	sub	= new subject_dao();

	public List<subject> subject_server(String seek1) throws SQLException
	{
		String sql = "SELECT d.`id`,d.`subjectCode`,d.`subjectName`,b.`tName`,e.`gradeName`FROM tclass a  "
				+ "INNER JOIN teacher b ON a.`tId`= b.id  "
				+ "INNER JOIN classtable c ON a.`classId` = c.id  "
				+ "INNER JOIN SUBJECT d ON a.`subId` = d.id "
				+ "INNER JOIN grade e ON d.`gradeId` = e.`id`";
		if (seek1 != null && !seek1.trim().equals(""))
			sql += " and (subjectCode like '%" + seek1
					+ "%' or subjectName like '%" + seek1
					+ "%' or tName like '%" + seek1 + "%' or gradeName like '%"
					+ seek1 + "%' )";
		return sub.show_subject(sql);
	}

	public List<subject> stu_subject_server(String seek1, String ID)
			throws SQLException
	{
		String sql = "SELECT * FROM student a LEFT JOIN classtable b ON a.`classID` = b.`id` WHERE stuCode='"
				+ ID + "'";
		int gradeID = sub.showgradeID(sql);
		sql = "SELECT d.`id`,d.`subjectCode`,d.`subjectName`,b.`tName`,e.`gradeName`FROM tclass a  "
				+ "INNER JOIN teacher b ON a.`tId`= b.id  "
				+ "INNER JOIN classtable c ON a.`classId` = c.id  "
				+ "INNER JOIN SUBJECT d ON a.`subId` = d.id "
				+ "INNER JOIN grade e ON d.`gradeId` = e.`id`";
		sql += "where d.gradeID = " + gradeID;
		if (seek1 != null && !seek1.trim().equals(""))
			sql += " and (subjectCode like '%" + seek1
					+ "%' or subjectName like '%" + seek1
					+ "%' or tName like '%" + seek1 + "%' or gradeName like '%"
					+ seek1 + "%' )";
		return sub.show_subject(sql);
	}

	public List<subject> cheek_subject() throws SQLException
	{
		String sql = "select * from subject";
		return sub.cheek_subject(sql);
	}
}
